package onlinebanking.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.GregorianCalendar;

/**
 * Class used to execute SQL commands from the Object Layer to the Database Layer
 * @author Del Mundo
 *
 */
public class SQLTable {
	/**
	 * The connection to the database driver
	 */
	protected Connection connection;
	/**
	 * The prepared sql statement
	 */
	protected PreparedStatement prepsqlstatement;
	
	
	public SQLTable(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * Sets and prepares the SQL statement for execution
	 * @param sql SQL statement. Example "SELECT username, password FROM users;"
	 */
	public void setSQLStatement(String sql){
		prepareSQLStatement(sql);
	}
	
	/**
	 * Executes the prepared SQL SELECT statement
	 * @return Resultset of the SQL statment. Null if error occurs
	 */
	public ResultSet executeSQL() {
	
		ResultSet rs = null;
		try {
			// TODO Handle SQL ERRORS like UNIQUE KEY violations
			
			// returns true if the sqlstatement is a SELECT statement with results
			if(prepsqlstatement.execute()) {
				rs = prepsqlstatement.getResultSet();				
			}
			

		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			System.out.println(ex);
		}
		return rs;
	}
	
	/**
	 * Executes an UPDATE,INSERT or DELETE statement; returns the primary key
	 * @return primary key of the updated record, -1 if error occurs
	 */
	public int executeUpdate(){		
		
		ResultSet keys;
		try {
			//Sets the SQL connection Auto-commit to false and make a transaction;
			connection.setAutoCommit(false);
			
			prepsqlstatement.executeUpdate();
			keys = prepsqlstatement.getGeneratedKeys();
			
			int  insertedPrimaryKey = -1;
			if(keys.next()){
				insertedPrimaryKey = keys.getInt(1);
			}
			
			keys.close();
			connection.commit();			
			connection.setAutoCommit(true);
			
			return insertedPrimaryKey;
		} catch (SQLException ex) {
			
			try {
				//roll backs the transaction if an error occured and restore autocommit to true
				if(!connection.getAutoCommit()){
					connection.rollback();
					System.out.println("Rolling back SQL transaction");
					ex.printStackTrace();
					connection.setAutoCommit(true);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			// TODO Auto-generated catch block
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			System.out.println(ex);;
			
		}
		return -1;
	}
	
	public int getColumnCount(ResultSet rs){
		try {
			ResultSetMetaData rsmetadata = rs.getMetaData();
			return rsmetadata.getColumnCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
		
	}
	/**
	 * Generates current timestamp
	 * @return Current timestamp
	 */
	public static Timestamp getNowTimeStamp(){
		return new Timestamp(new Date().getTime());
	}
	
	/**
	 * Prepares an SQL statement for this SQLTable object
	 * @param sql SQL statment
	 */
	private void prepareSQLStatement(String sql){
		try {
//			SQL Format
//		String sql = "SELECT personalaccountid,username,password FROM personalaccounts WHERE username = ?;";
			prepsqlstatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		} catch (SQLException e) {
			System.err.println("SQLException in Preparing SQL Statement "+e);
		}		
	}
	
	
	//==================================FOR TESTING PURPOSES  ==================================================
	public static void main(String[] args) throws SQLException {
		System.out.println("main-SQLTABLE");
		SQLConnection sqlconn =  SQLConnection.getInstance();
		SQLTable sqltable = new SQLTable(sqlconn.getConnection());
		System.out.println("READY");
		sqltable.setSQLStatement("SELECT personalaccountid,username,password FROM personalaccounts WHERE username = ?;");
	
		sqltable.setStringParam(1, "benj");
//		SQL Injection attempt
//		sqltable.setStringParam(1, "benj;insert into perosnalaccounts (username,password) values('virusako','viruspass');");
		ResultSet rs =  sqltable.executeSQL();
		
		
		while(rs.next()){
			String username = rs.getString("username");
			String password = rs.getString("password");
			int id = rs.getInt(1);
			
			System.out.println("info: "+ username+":"+password+":"+id);
		}
		
		
	}
	
	/**=================================== PREARED STAEMENT SETTERS ========================================================= */

	public void setBooleanParam(int parameterIndex, boolean bool){
		try {
			prepsqlstatement.setBoolean(parameterIndex, bool);
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public void setCharParam(int parameterIndex, char c){
		try {
			prepsqlstatement.setString(parameterIndex, c+"");
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public void setDateParam(int parameterIndex, Date date){
		try {
			if(date != null){
				prepsqlstatement.setDate(parameterIndex, (java.sql.Date) date);
			}else{
				prepsqlstatement.setNull(parameterIndex, java.sql.Types.NULL);
			}
			
		} catch (SQLException e) {e.printStackTrace();}
	}
	public void setDoubleParam(int parameterIndex, double x){
		try {
			prepsqlstatement.setDouble(parameterIndex, x);
		} catch (SQLException e) {e.printStackTrace();}
	}
	public void setIntParam(int parameterIndex, int x){
		try {
			prepsqlstatement.setInt(parameterIndex, x);
		} catch (SQLException e) {e.printStackTrace();}
	}
	public void setFloatParam(int parameterIndex, float x){
		try {
			if(!Float.isNaN(x)){
				prepsqlstatement.setFloat(parameterIndex, x);
			}else{
				prepsqlstatement.setNull(parameterIndex, java.sql.Types.NULL);
			}
		} catch (SQLException e) {e.printStackTrace();}
	}
	public void setLongParam(int parameterIndex, long x){
		try {
			prepsqlstatement.setLong(parameterIndex, x);
		} catch (SQLException e) {e.printStackTrace();}
	}
	public void setTimeStampParam(int parameterIndex, Timestamp timestamp){
		try {
			if(timestamp != null){
				prepsqlstatement.setTimestamp(parameterIndex, timestamp);
			}else{
				prepsqlstatement.setNull(parameterIndex, java.sql.Types.NULL);
			}
			
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public void setNowTimeStampParam(int parameterIndex){
		try {
			prepsqlstatement.setTimestamp(parameterIndex, SQLTable.getNowTimeStamp());
			
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public void setStringParam(int parameterIndex, String x){
		try {
			if(x != null){
				prepsqlstatement.setString(parameterIndex, x);
			}else{
				prepsqlstatement.setNull(parameterIndex, java.sql.Types.NULL);
			}
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public void setNullParam(int parameterIndex){
		try {
			prepsqlstatement.setNull(parameterIndex, java.sql.Types.NULL);
		} catch (SQLException e) {e.printStackTrace();
		}
	}
	
}
